package allenja.cp2011.MVC;

import allenja.cp2011.MVC.DbConnectionFactory;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DerbyJdbcDAO {
	Connection con = null;
	PreparedStatement preparedStatement = null;
	ResultSet resultSet = null;

	// Default constructor
	public DerbyJdbcDAO() {

	}

	// Create a connection using the connection factory
	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = DbConnectionFactory.getInstance().getConnection();
		return conn;
	}
	
	// Create the chat users table
	public void createChatUsersTable() {
		// Use the metadata object ensure the table exists, if not exists will be created
		DatabaseMetaData dbMetaData;
		
		try {
			dbMetaData = getConnection().getMetaData();
			ResultSet metadataResultSet = dbMetaData.getTables(null, null, "CHATSERVERUSERS", null);
			
			if (!metadataResultSet.next()) {
				String createTable = "create table ChatServerUsers (UserName varchar(6), userID int not null generated by default as identity" +
						"(start with 1, increment by 1))";
				PreparedStatement createTableStatement = getConnection().prepareStatement(createTable);
				createTableStatement.execute();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// Create the forum table
	public void createForumTable() {
		// Use the metadata object ensure the table exists, if not exists will be created
		DatabaseMetaData dbMetaData;
		
		try {
			dbMetaData = getConnection().getMetaData();
			ResultSet metadataResultSet = dbMetaData.getTables(null, null, "FORUMREPOSITORY", null);
			
			if (!metadataResultSet.next()) {
				String createTable = "create table ForumRepository (ForumName varchar(30) primary key, forumDescription varchar(512) not null," +
						" forumCreater varchar(6) not null, forumID int not null generated by default as identity (start with 1, increment by 1))";
				
				PreparedStatement createTableStatement = getConnection().prepareStatement(createTable);
				createTableStatement.execute();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// Create the forum messages table
	public void createForumMessagesTable() {
		// Use the metadata object ensure the table exists, if not exists will be created
		DatabaseMetaData dbMetaData;
		
		try {
			dbMetaData = getConnection().getMetaData();
			ResultSet metadataResultSet = dbMetaData.getTables(null, null, "FORUMMESSAGES", null);
			
			if (!metadataResultSet.next()) {
				String createTable = "create table ForumMessages (forumName varchar(30) not null, forumMessage varchar(512) not null, forumMessageCreater" +
						" varchar(12) not null, forumMessageCreationDateTime timestamp not null, forumMessageID int primary key not null generated by default as identity (start with 1, increment by 1))";
				
				PreparedStatement createTableStatement = getConnection().prepareStatement(createTable);
				createTableStatement.execute();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// Create an inner join from the forum table to the forum messages table using forum name as the link
	public void createForumTableToForumMessagesTableJoin() {
		String createJoin = "SELECT ForumRepository.forumName, ForumMessages.ForumName " +
				"FROM ForumRepository INNER JOIN ForumMessages ON ForumMessages.forumName=ForumMessages.ForumName " +
				"ORDER BY ForumRepository.forumName";
		
		try {
			PreparedStatement createJoinStatement = getConnection().prepareStatement(createJoin);
			createJoinStatement.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	// Add a new forum message to the database
	public void addForumMessage(ForumMessageBean forumMessageBean) {
		try {
			String createForumMessageString = "insert into ForumMessages values (?,?,?,?,?)";
			con = getConnection();
			preparedStatement = con.prepareStatement(createForumMessageString);
			preparedStatement.setString(1, forumMessageBean.getForumName());
			preparedStatement.setString(2, forumMessageBean.getForumMessage());
			preparedStatement.setString(3, forumMessageBean.getForumMessageCreater());
			preparedStatement.setTimestamp(4, forumMessageBean.getForumMessageCreationDateTime());
			preparedStatement.setInt(5, forumMessageBean.getForumMessageID());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	// Delete a forum message from the database
	public void deletForumMessage(ForumMessageBean forumMessageBean) {
		try {
			String removeForumMessageString = "delete from ForumMessages where forumMessageID = ?";
			con = getConnection();
			preparedStatement = con.prepareStatement(removeForumMessageString);
			preparedStatement.setInt(4, forumMessageBean.getForumMessageID());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
	}
	
	// Select and return all forum messages in the database
	public List<ForumMessageBean> findAllForumMessages() {
		List<ForumMessageBean> messages = new ArrayList<ForumMessageBean>();
		ForumMessageBean forumMessageBean = null;
		
		try {
			String selectAllForumMessagesString = "select * from ForumMessages";
			con = getConnection();
			preparedStatement = con.prepareStatement(selectAllForumMessagesString);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				forumMessageBean = new ForumMessageBean();
				forumMessageBean.setForumName(resultSet.getString(1));
				forumMessageBean.setForumMessage(resultSet.getString(2));
				forumMessageBean.setForumMessageCreater(resultSet.getString(3));
				forumMessageBean.setForumMessageCreationDateTime(resultSet.getTimestamp(4));
				forumMessageBean.setForumMessageID(resultSet.getInt(5));
				messages.add(forumMessageBean);
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return messages;
	}
	
	// Get the next forum message ID from the database
	public int getNextForumMessageID() {
		int value = findAllForumMessages().size() + 1;
		return value;
	}
	
	// Get the next forum ID from the database
	public int getNextForumID() {
		int value = findAllForums().size() + 1;
		return value;
	}
	
	// Select and return all forums in the database
	public List<ForumBean> findAllForums() {
		List<ForumBean> forums = new ArrayList<ForumBean>();
		ForumBean forumBean = null;

		try {
			String selectAllForumsString = "select * from ForumRepository";
			con = getConnection();
			preparedStatement = con.prepareStatement(selectAllForumsString);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				forumBean = new ForumBean();
				forumBean.setForumName(resultSet.getString(1));
				forumBean.setForumDesription(resultSet.getString(2));
				forumBean.setforumCreator(resultSet.getString(3));
				forumBean.setForumID(resultSet.getInt(4));
				forums.add(forumBean);
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return forums;
	}
	
	// Add a new forum to the database
	public void addForum(ForumBean forumBean) {
		try {
			String createForumString = "insert into ForumRepository values (?,?,?,?)";
			con = getConnection();
			preparedStatement = con.prepareStatement(createForumString);
			preparedStatement.setString(1, forumBean.getForumName());
			preparedStatement.setString(2, forumBean.getForumDescription());
			preparedStatement.setString(3, forumBean.getforumCreator());
			preparedStatement.setInt(4, forumBean.getForumID());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	// Delete a forum from the database
	public void deleteForum(String forumName) {
		try {
			String removeForumString = "delete from ForumRepository where forumName = ?";
			con = getConnection();
			preparedStatement = con.prepareStatement(removeForumString);
			preparedStatement.setString(1, forumName);
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
	}
	
	// Get the next user ID from the database
	public int getNextUserID() {		
		int value = findAllChatUsers().size() + 1;
		return value;
	}

	// Add a new chat user to the database
	public void addChatUser(ChatUserBean chatUserBean) {
		try {
			String createUserString = "insert into ChatServerUsers values (?,?)";
			con = getConnection();
			preparedStatement = con.prepareStatement(createUserString);
			preparedStatement.setString(1, chatUserBean.getUserName());
			preparedStatement.setInt(2, chatUserBean.getUserID());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// Delete a chat user
	public void deleteChatUser(String userName, int userID) {
		try {
			String removeUserString = "delete from ChatServerUsers where username = ?";
			con = getConnection();
			preparedStatement = con.prepareStatement(removeUserString);
			preparedStatement.setString(1, userName);
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
	}

	// Select and return all chat users in the database
	public List<ChatUserBean> findAllChatUsers() {
		List<ChatUserBean> users = new ArrayList<ChatUserBean>();
		ChatUserBean chatUserBean = null;

		try {
			String selectAllUsersString = "select * from ChatServerUsers";
			con = getConnection();
			preparedStatement = con.prepareStatement(selectAllUsersString);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				chatUserBean = new ChatUserBean();
				chatUserBean.setUserName(resultSet.getString(1));
				chatUserBean.setUserID(resultSet.getInt(2));
				users.add(chatUserBean);
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return users;
	}

	// Find a chat user in the database by user name
	public ChatUserBean findByChatUsername(String userName) {
		ChatUserBean chatUserBean = null;

		try {
			String selectUserByUsername = "select * from ChatServerUsers where userName = ?";
			con = getConnection();
			preparedStatement = con.prepareStatement(selectUserByUsername);
			preparedStatement.setString(1, userName);
			resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				chatUserBean = new ChatUserBean();
				chatUserBean.setUserName(resultSet.getString(1));
				chatUserBean.setUserID(resultSet.getInt(2));
			}

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {

				if (resultSet != null) {
					resultSet.close();
				}

				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return chatUserBean;
	}
}
